﻿using StudentsManager.Exceptions;
using StudentsManager.Models;
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace StudentsManager.DataAccess
{
    public class SchoolDataAccess : DatabaseHelper
    {
        // 添加学校
        public void AddSchool(School school)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                connection.Open();
                var command = new SQLiteCommand("INSERT INTO Schools (Name, Address) VALUES (@name, @address)", connection);
                command.Parameters.AddWithValue("@name", school.Name);
                command.Parameters.AddWithValue("@address", school.Address);
                command.ExecuteNonQuery();
            }
        }

        // 更新学校
        public void UpdateSchool(School school)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                connection.Open();
                var command = new SQLiteCommand("UPDATE Schools SET Name = @name, Address = @address WHERE Id = @id", connection);
                command.Parameters.AddWithValue("@name", school.Name);
                command.Parameters.AddWithValue("@address", school.Address);
                command.Parameters.AddWithValue("@id", school.Id);
                command.ExecuteNonQuery();
            }
        }

        // 删除学校（级联删除时抛出异常）
        public void DeleteSchool(int schoolId)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                connection.Open();

                // 检查是否有班级与该学校相关联
                var classCommand = new SQLiteCommand("SELECT COUNT(*) FROM Classes WHERE SchoolId = @schoolId", connection);
                classCommand.Parameters.AddWithValue("@schoolId", schoolId);
                int classCount = Convert.ToInt32(classCommand.ExecuteScalar());

                if (classCount > 0)
                {
                    throw new CascadeDeleteException("无法删除该学校，因为存在相关的班级或学生记录。");
                }

                // 如果没有相关记录，执行删除操作
                var deleteCommand = new SQLiteCommand("DELETE FROM Schools WHERE Id = @id", connection);
                deleteCommand.Parameters.AddWithValue("@id", schoolId);
                deleteCommand.ExecuteNonQuery();
            }
        }

        // 查询所有学校
        public List<School> GetSchoolList()
        {
            return GetSchoolList(null);
        }

        // 查询学校（根据school中的信息）
        public List<School> GetSchoolList(School school)
        {
            List<School> res = new List<School>();
            // 根据SchoolForm中获取的信息查询学校，如果school为null，则查询所有学校
            using (var connection = new SQLiteConnection(_connectionString))
            {
                connection.Open();
                string query = "SELECT * FROM Schools WHERE 1=1";
                if (school != null)
                {
                    if (!string.IsNullOrEmpty(school.Name))
                    {
                        query += " AND Name LIKE @name";
                    }
                    if (!string.IsNullOrEmpty(school.Address))
                    {
                        query += " AND Address LIKE @address";
                    }
                }

                using (var command = new SQLiteCommand(query, connection))
                {
                    if (school != null)
                    {
                        if (!string.IsNullOrEmpty(school.Name))
                        {
                            command.Parameters.AddWithValue("@name", $"%{school.Name}%");
                        }
                        if (!string.IsNullOrEmpty(school.Address))
                        {
                            command.Parameters.AddWithValue("@address", $"%{school.Address}%");
                        }
                    }

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            res.Add(new School
                            {
                                Id = Convert.ToInt32(reader["Id"]),
                                Name = reader["Name"].ToString(),
                                Address = reader["Address"].ToString()
                            });
                        }
                    }
                }
            }
            return res;
        }
    }
}
